package com.wy.tingshu.repository;

import com.wy.tingshu.data_object.TrackDO;
import com.wy.tingshu.util.DBUtil;
import com.wy.tingshu.util.Log;
import lombok.SneakyThrows;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 仓库对象：单表直接访问数据库（Database）的相关操作
 * 直接操作track表
 **/
public class TrackRepo {
    //音频插入数据库
    @SneakyThrows//lombok下的，可以让我们不添加throws也可以正常抛出受查异常
    public void insert(Integer uid, String title, String type, InputStream contentInputStream) {
        String sql="insert into tracks(uid,title,type,content) values(?,?,?,?)";
        try (Connection c= DBUtil.connection()){
            try (PreparedStatement ps=c.prepareStatement(sql)){
                ps.setInt(1,uid);
                ps.setString(2,title);
                ps.setString(3,type);
                ps.setBinaryStream(4,contentInputStream);
                    //数据库表中的longblob类型就是一种二进制流<->BinaryStream。向其传入二进制数据
                Log.println("执行SQL："+ps.toString());
                ps.executeUpdate();


            }

        }
    }
    //根据数据库音频表track中的uid查询tid和title（查询当前页音频列表）
    @SneakyThrows
    public List<TrackDO> selectListByUidLimitOffset(Integer uid,int limit,int offset) {
        //不要一次查太多的content字段，因为content比较大，一次查太多会导致计算机负载很高
        String sql="select tid,uid,title,type from tracks where uid=? order by tid desc limit ? offset ?";
        List<TrackDO> list=new ArrayList<>();
        try (Connection c=DBUtil.connection()){
            try (PreparedStatement ps=c.prepareStatement(sql)){
                ps.setInt(1,uid);//第一个问号是uid
                ps.setInt(2,limit);
                ps.setInt(3,offset);
                Log.println("执行SQL"+ps);
                try (ResultSet rs=ps.executeQuery()){
                    while (rs.next()){
                       TrackDO trackDO=new TrackDO(
                               rs.getInt("tid"),
                               rs.getInt("uid"),
                               rs.getString("title"),
                               rs.getString("type")
                       );
                       list.add(trackDO);
                    }
                }
            }

        }
        return list;
    }

    //查询出uid=?的音频内容总数
    @SneakyThrows//不添加throws也可以正常抛出受查异常
    public int selectCountByUid(int uid) {
        String sql="select count(*) from tracks where uid=?";
        try (Connection c=DBUtil.connection()){
            try(PreparedStatement ps=c.prepareStatement(sql)){
                ps.setInt(1,uid);
                Log.println("执行SQL:"+ps);
                try (ResultSet rs=ps.executeQuery()){
                    rs.next();//肯定是一条，无需循环判断
                    return rs.getInt(1);
                }
            }
        }
    }

    //通过selectListByAid查到的tid在音频表中找到对应音频的tid uid title type
    @SneakyThrows
    public List<TrackDO> selectListByTidList(List<Integer> tidList) {//加入放的[1,2,3,4]
        //进行拼接:将selectListByAid查到的所有tid拼接成字符串
        //1.List<Integer>转为List<String>     // ["1","2","3","4"]
        //2.调用String.join(", ",List<String>)//join:加入连接，用逗号空格连接 //"1, 2, 3, 4"
        String s = String.join(", ",tidList.stream().map(String::valueOf).collect(Collectors.toList()));//String.valueOf(i):整型转为String类的。i->String.valueOf(i)<=>String::valueOf,双冒号表示调我String类下的valueOf方法

        String sql=String.format("select tid,uid,title,type from tracks where tid in(%s) order by tid",s);
        List<TrackDO> list=new ArrayList<>();
        try (Connection c=DBUtil.connection()){
            try (PreparedStatement ps=c.prepareStatement(sql)){
                Log.println("执行SQL:"+ps);
                try (ResultSet rs= ps.executeQuery()){
                    while (rs.next()){
                        list.add(new TrackDO(
                           rs.getInt("tid"),
                           rs.getInt("uid"),
                           rs.getString("title"),
                           rs.getString("type")
                        ));
                    }
                }
            }
        }
        return list;
    }
}
